import datetime
import django
import os
import sys

import xlrd

sys.path.append('E:\django\dads_gas_django')
os.environ['DJANGO_SETTINGS_MODULE'] = 'gas.settings'
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "project_name.settings")
django.setup()
from api.models import Record, User
from xpinyin import Pinyin


class GASFileUtils:

    @classmethod
    def getUserNameInFile(cls, filePath):
        execl = xlrd.open_workbook(filePath)
        list = []
        for table in execl.sheets():
            col = table.col_values(0)
            list += col
        return list

    @classmethod
    def getTableData(cls, table):
        print('读取{}'.format(table.name))
        list = []
        for l in range(table.nrows):
            row = table.row_values(l)
            row[0] = row[0].replace('仔', '子', -1)
            for i in range(len(row)):
                if i > 0 and i % 4 == 0 and type(row[i]) == float:
                    row[i] = xlrd.xldate_as_datetime(row[i], 0)
                if i % 4 == 1:
                    if row[i] == '':
                        row = row[:i]
                        break
            for i in range(len(row)):
                if i % 4 == 1:
                    if row[i].startswith('2'):
                        row[i] = row[i][1]
                        row2 = row[i:i + 4]
                        for j in range(4):
                            row.append(row2[j])
                    elif row[i].startswith('3'):
                        row[i] = row[i][1]
                        row2 = row[i:i + 4]
                        for j in range(4):
                            row.append(row2[j])
                        for j in range(4):
                            row.append(row2[j])
                    elif row[i].startswith('4'):
                        row[i] = row[i][1]
                        row2 = row[i:i + 4]
                        for j in range(4):
                            row.append(row2[j])
                        for j in range(4):
                            row.append(row2[j])
                        for j in range(4):
                            row.append(row2[j])
                    elif row[i] == '热':
                        row[i] = '大'
                    elif row[i] == '大新':
                        row[i] = '新'
                    elif row[i] == '吉':
                        row[i] = '中'
                    elif row[i] == '不':
                        row[i] = '小'
                if type(row[i]) == str:
                    row[i] = row[i].replace(' ', '')
                    if row[i].startswith('￥'):
                        row[i] = row[i].replace('￥', '')
                        row[i] = int(row[i])
            list.append(row)
        return list

    @classmethod
    def getData(cls, filePath):
        execl = xlrd.open_workbook(filePath)
        list = []
        # return GASFileUtils.getTableData(execl.sheet_by_index(10))
        # for i in range(len(execl.sheets()) - 11):
        #     list += GASFileUtils.getTableData(execl.sheet_by_index(i + 10))
        for table in execl.sheets():
            list += GASFileUtils.getTableData(table)
        return list


def insertDate(dataPath):
    starttime = datetime.datetime.now()
    print('开始获取{}的数据'.format(dataPath))
    data = GASFileUtils.getData(dataPath)
    print('已经获取{}全部数据，正在导入...'.format(dataPath))
    a = b = 0
    queryset_list = []
    for d in data:
        user, i = User.objects.get_or_create(name=d[0])
        if (i):
            a += 1
        for i in range(int(len(d) / 4)):
            try:
                # record = Record(user=user, payed=d[i * 4 + 3] == '√', money=d[i * 4 + 2],
                #                 type=d[i * 4 + 1].replace(' ', ''),
                #                 date=datetime.date.fromtimestamp((d[i * 4 + 4] - 25569) * 24 * 60 * 60))
                record = Record(user=user, payed=d[i * 4 + 3] == '√', money=d[i * 4 + 2],
                                type=d[i * 4 + 1].replace(' ', ''),
                                date=d[i * 4 + 4])
                queryset_list.append(record)
            except Exception:
                print('插入异常：{} {} {} {} {}'.format(d[0], d[i * 4 + 1], d[i * 4 + 2], d[i * 4 + 3], d[i * 4 + 4]))
                pass
    b += len(queryset_list)
    Record.objects.bulk_create(queryset_list)
    print('导入{}成功\n用时：{}秒\n共导入姓名{}个,数据{}个'.format(dataPath, datetime.datetime.now() - starttime, a, b))


if __name__ == '__main__':
    base = 'E:/django/gas/resource/'
    print('开始获取文件...')
    filePath = None
    for files in os.walk(base):
        filePath = files[2]
    for file in filePath:
        insertDate(base + file)
